DROP TABLE ExecQueryStats

CREATE TABLE ExecQueryStats
(
	startTime datetime,
	endTime datetime,
	plan_handle varbinary(64),
	statement_id int,
	statement_text nvarchar(max),
	delta_execution_count bigint,
	l_total_execution_count bigint,
	delta_worker_time bigint,
	l_total_worker_time bigint,
	delta_physical_reads bigint,
	l_total_physical_reads bigint,
	delta_logical_reads bigint,
	l_total_logical_reads bigint,
	delta_logical_writes bigint,
	l_total_logical_writes bigint,
	delta_elapsed_time bigint,
	l_total_elapsed_time bigint,
	PRIMARY KEY NONCLUSTERED(statement_id,startTime)
)

CREATE CLUSTERED INDEX idx_clustered ON ExecQueryStats(startTime)
GO
DROP PROCEDURE copyDMV
GO

CREATE PROCEDURE copyDMV 
AS
BEGIN
	INSERT INTO ExecQueryStats
		SELECT 
			ISNULL(e.endTime,'1/1/2000 0:00:00') 'startTime',
			GETUTCDATE() 'endTime',
			dmv.plan_handle 'plan_handle',
			CHECKSUM(dmv.plan_handle) 'statement_id',
			dmv.statement_text 'statement_text',
			dmv.execution_count-ISNULL(e.l_total_execution_count,0) 'delta_execution_count',
			dmv.execution_count 'l_total_execution_count',
			dmv.total_worker_time-ISNULL(e.l_total_worker_time,0) 'delta_worker_time',
			dmv.total_worker_time 'l_total_worker_time',
			dmv.total_physical_reads-ISNULL(e.l_total_physical_reads,0) 'delta_physical_reads',
			dmv.total_physical_reads 'l_total_physical_reads',
			dmv.total_logical_reads-ISNULL(e.l_total_logical_reads,0) 'delta_logical_reads',
			dmv.total_logical_reads 'l_total_logical_reads',
			dmv.total_logical_writes-ISNULL(e.l_total_logical_writes,0) 'delta_logical_writes',
			dmv.total_logical_writes 'l_total_logical_writes',
			dmv.total_elapsed_time-ISNULL(e.l_total_elapsed_time,0) 'delta_elapsed_time',
			dmv.total_elapsed_time 'l_total_elapsed_time'
		FROM
			(
			SELECT 
				SUBSTRING(r.text, (t.statement_start_offset/2)+1, 
					((CASE t.statement_end_offset
					  WHEN -1 THEN DATALENGTH(r.text)
					 ELSE t.statement_end_offset
					 END - t.statement_start_offset)/2) + 1) 'statement_text'
			,t.plan_handle,t.creation_time,t.last_execution_time,t.execution_count,t.total_worker_time,t.total_physical_reads,t.total_logical_reads,t.total_logical_writes,t.total_elapsed_time
			FROM sys.dm_exec_query_stats t 
				CROSS APPLY sys.dm_exec_sql_text(t.sql_handle) r 
			) dmv LEFT OUTER JOIN
			(SELECT 
				statement_text,
				plan_handle,
				endTime,
				delta_execution_count,
				l_total_execution_count,
				delta_worker_time,
				l_total_worker_time,
				delta_physical_reads,
				l_total_physical_reads,
				delta_logical_reads,
				l_total_logical_reads,
				delta_logical_writes,
				l_total_logical_writes,
				delta_elapsed_time,
				l_total_elapsed_time,
				ROW_NUMBER() OVER(PARTITION BY plan_handle,statement_text ORDER BY endTime desc) 'rownum'
			FROM ExecQueryStats
			) e ON dmv.statement_text=e.statement_text and dmv.plan_handle=e.plan_handle
		WHERE e.rownum=1 OR e.rownum is null
END

